import string
from pathlib import Path
import pandas as pd

import xlrd
# excel转dict
import pandas
# from xlrd import xldate_as_datetime
from app import useDB

def read_excel(path):
    work_book = xlrd.open_workbook(path)
    # work_book = pandas.read_excel(path, engine ='openpyxl')
    print(work_book)
    work_sheet = work_book.sheet_by_index(0)

    # 获取总行数
    total_rows = work_sheet.nrows

    # 定义一个列表，用于存放每一行的内容
    data_list = []
    key_list = None
    for i in range(total_rows):
        # 获取每一行的内容
        row_data = work_sheet.row_values(i)
        if i == 0:
            # 将第一行的作为字典的key
            key_list = row_data
        else:

            data_dict = dict()
            for index, cel_data in enumerate(row_data):
                # 获取字典的key
                key = key_list[index]

                # 注意点01 : 如果该单元格存放的是日期类型，读入的时候，需要借助 xldate_as_datetime 模块
                # 具体的用法请自行百度
                if index == 7:
                    if cel_data:
                        cel_data = xldate_as_datetime(cel_data, 0)
                else:
                    # 注意点02: 单元格是数字的，读取时会识别为浮点数，特此处理一下
                    if isinstance(cel_data, float):
                        cel_data = int(cel_data)

                # 字典赋值
                data_dict[key] = cel_data
            # 将字典加入需要返回的列表中
            data_list.append(data_dict)
    print(data_list)
    # new_test_case_2(data_list)

def new_test_case_2(datalist):
    # module
    # 模块1
    # name
    # 1212
    # steps
    # 42
    # r543grtg
    # description
    # isPublic

    con=useDB.useDB()
    for data in datalist:
        steps = str(data["steps"]).replace('\\', '\\\\')
        sql = string.Template(
        'insert into test_case (module,name,steps,description,isPublicFunction) values ("$module","$name","$steps","$description",$isPublic);')
    sql = sql.substitute(name=data["name"], module=data["module"], steps=steps, description=data["description"], isPublic=data["isPublicFunction"])
    con.insert(sql)





if __name__ == '__main__':
    read_excel("/Users/gabriel/Desktop/test1.xls")